﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetDocumentPostil_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetDocumentPostil_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetDocumentPostil_V2]
    @DocID int
AS
BEGIN
SET NOCOUNT ON;

-- Get the date and time when the document got created.
DECLARE @docCreationTime datetime;
SELECT @docCreationTime = doc_added_date FROM dbo.UDS_Flow_Document WHERE DOC_ID = @DocID;
IF ( @@ROWCOUNT = 0 )
    RETURN -1;      -- document not found!

-- select all the postils
SELECT 
        s.realname,
        p1.postil_content,
        p1.postil_date,
        p1.postil_type,
        (
            SELECT DATEDIFF(minute, ISNULL(MAX(p2.Postil_Date), @docCreationTime), p1.Postil_Date)
                FROM dbo.UDS_Flow_Postil p2
                WHERE p2.Doc_ID = @DocID AND p2.Postil_Date < p1.Postil_Date
        ) as UsedTimes,
        f.*
    FROM
        dbo.UDS_Flow_Postil p1
        INNER JOIN dbo.uds_staff s ON p1.Postiler_ID = s.Staff_ID
        LEFT OUTER JOIN dbo.uds_flow_files f ON p1.[File_ID] = f.FileID
    WHERE
        p1.Doc_ID = @DocId
    ORDER BY p1.Postil_Date;

RETURN 0;

END
GO